package com.neusoft.realsight.apm.action;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.neusoft.realsight.apm.dao.ConnectorFactory;
import com.neusoft.realsight.apm.dao.ExcelDao;
import com.neusoft.realsight.apm.model.Catalog;
import com.neusoft.realsight.apm.model.Column;
import com.neusoft.realsight.apm.model.RowData;
import com.neusoft.realsight.apm.model.Table;
import com.neusoft.realsight.apm.util.GlobalVar;

/**
 * Created by Administrator on 2016/6/10.
 */
public class Main {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			conn = ConnectorFactory.getMysqlConnection();
			ResultSet rs = ConnectorFactory.query(conn, GlobalVar.QUERY_SQL);
			List<RowData> rdList =convertList(rs);
			List<RowData> rdList1 = new ArrayList<RowData>();
			List<RowData> rdList2 = new ArrayList<RowData>();
			List<RowData> rdList3 = new ArrayList<RowData>();
			for (int i = 0; i < rdList.size(); i++) {
				String id = rdList.get(i).getId();
				String pId = rdList.get(i).getpId();
				String name = rdList.get(i).getName();
				if (id.length() == 3) {
					RowData rd = new RowData();
					List<String> chidNameList = new ArrayList<String>();
					rd.setId(id);
					rd.setpId(pId);
					rd.setName(name);
					rd.setChildName(chidNameList);
					rdList1.add(rd);
				}
				if (id.length() == 3) {
					RowData rd = new RowData();
					List<String> chidNameList = new ArrayList<String>();
					rd.setId(id);
					rd.setpId(pId);
					rd.setName(name);
					rd.setChildName(chidNameList);
					for (int j = 0; j < rdList.size(); j++) {
						if (rdList.get(j).getId().length() == 6 && rdList.get(j).getId().substring(0, 3).equals(id)) {
							rd.getChildName().add(rdList.get(j).getName());
						}
					}
					rdList2.add(rd);
				}
				if (id.length() == 6) {
					RowData rd = new RowData();
					List<String> chidNameList = new ArrayList<String>();
					rd.setId(id);
					rd.setpId(pId);
					rd.setName(name);
					rd.setChildName(chidNameList);
					for (int j = 0; j < rdList.size(); j++) {
						if (rdList.get(j).getId().length() == 9 && rdList.get(j).getId().substring(0, 6).equals(id)) {
							rd.getChildName().add(rdList.get(j).getName());
						}
					}
					rdList3.add(rd);
				}
			}
			rdList1.addAll(rdList2);
			rdList1.addAll(rdList3);
			writeInExcel(rdList1, "E:\\");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("连接器获取异常");
		}
		System.out.println("good");

		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static List<RowData> convertList(ResultSet rs) throws SQLException {
		List<RowData> list = new ArrayList<RowData>();
		ResultSetMetaData md = rs.getMetaData();// 获取键名
		int columnCount = md.getColumnCount();// 获取行的数量
		while (rs.next()) {
			RowData rd = new RowData();
			for (int i = 1; i <= columnCount; i++) {
				rd.setId(rs.getObject("id").toString());
				rd.setpId(rs.getObject("pid").toString());
				rd.setName(rs.getObject("name").toString());
			}
			list.add(rd);
		}
		return list;
	}

	public static void writeInExcel(List<RowData> rdList, String path) {

		XSSFWorkbook wb = ExcelDao.createHSSFWorkBook();
		XSSFSheet sheet = ExcelDao.createSheet(wb, "test");

		String wbPath = path + "/test.xls";
		for (int i = 0; i < rdList.size(); i++) {
			RowData rd = rdList.get(i);
			ExcelDao.addContentInCell(rd.getId(), sheet, null, i, 0, null);
			ExcelDao.addContentInCell(rd.getpId(), sheet, null, i, 1, null);
			ExcelDao.addContentInCell(rd.getName(), sheet, null, i, 2, null);
			if (rd.getChildName() != null) {
				for (int j = 0; j < rd.getChildName().size(); j++) {
					ExcelDao.addContentInCell(rd.getChildName().get(j), sheet, null, i, j + 3, null);
				}

			}
		}

		// for (Table t : catalog.getTables()) {
		// HSSFSheet sheet = ExcelDao.createSheet(wb,
		// (t.getRemark() == null || "".equals(t.getRemark().trim())) ?
		// t.getTableName() : t.getRemark());
		// ExcelDao.addContentInCell("表名", sheet, null, 0, 0, null);
		// ExcelDao.addContentInCell(t.getTableName(), sheet, null, 0, 1,
		// null);
		// ExcelDao.addContentInCell("备注", sheet, null, 1, 0, null);
		// ExcelDao.addContentInCell(t.getRemark(), sheet, null, 1, 1,
		// null);
		// ExcelDao.addContentInCell("表类型", sheet, null, 2, 0, null);
		// ExcelDao.addContentInCell(t.getType(), sheet, null, 2, 1, null);
		//
		// ExcelDao.addContentInCell("列名", sheet, null, 3, 0, null);
		// ExcelDao.addContentInCell("类型", sheet, null, 3, 1, null);
		// ExcelDao.addContentInCell("默认值", sheet, null, 3, 2, null);
		// ExcelDao.addContentInCell("是否可以为空", sheet, null, 3, 3, null);
		// ExcelDao.addContentInCell("备注", sheet, null, 3, 4, null);
		//
		// int rowcount = 4;
		// for (Column c : t.getColumns()) {
		// ExcelDao.addContentInCell(c.getColumnName(), sheet, null,
		// rowcount, 0, null);
		// ExcelDao.addContentInCell(c.getColumnType() + "(" +
		// c.getColumnSize() + ")", sheet, null, rowcount,
		// 1, null);
		// ExcelDao.addContentInCell(c.getDefultValue(), sheet, null,
		// rowcount, 2, null);
		// ExcelDao.addContentInCell(c.getIsNullable() ==
		// Column.NULLINFO.NULLABLE ? "Y" : "N", sheet, null,
		// rowcount, 3, null);
		// ExcelDao.addContentInCell(c.getRemark(), sheet, null, rowcount,
		// 4, null);
		// rowcount++;
		// }
		//
		// }
		ExcelDao.saveHSSFWorkBook(wb, wbPath);
	}
}
